/*** 
This do-file processes BLS data on historical employment at the county level.
***/

*-------------------------------------------------------------------------------
* Set up 
*-------------------------------------------------------------------------------
* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"

* Create required subfolders
local category "Employment"
cap mkdir "${root}/data"
cap mkdir "${root}/data/derived"
cap mkdir "${root}/data/derived/BLS"

*-------------------------------------------------------------------------------
**# 1. Get annual averages for historical recession years 
*-------------------------------------------------------------------------------

foreach year in "00" "01" "07" "10" "90" "91" {

	project, uses("${root}/data/dvc/BLS/laucnty`year'.xlsx")
	import excel "${root}/data/dvc/BLS/laucnty`year'.xlsx", clear
	
	keep B C G H I J
	rename B statefips
	rename C countyfips
	rename G labor_force_`year'
	rename H employed_`year' 
	rename I unemployed_`year' 
	rename J unemp_rate_`year' 
	
	drop if _n < 7
	
	destring statefips countyfips labor_force_`year' employed_`year' unemployed_`year' unemp_rate_`year', replace
	replace countyfips = statefips * 1000 + countyfips
	drop if mi(countyfips)
	drop if statefips == 72 
	
	gisid countyfips
	gunique statefips 
	assert r(unique) == 51
	
	tempfile laucnty`year'
	save `laucnty`year'', replace 
}

*-------------------------------------------------------------------------------
**# 2. Get monthly data in 2020 
*-------------------------------------------------------------------------------
 
* Create monthly county level emmployment data from BLS data
project, uses("${root}/data/dvc/BLS/laucntycur14.txt")
import delimited "${root}/data/dvc/BLS/laucntycur14.txt", delimiter("|") varnames(6) encoding(ISO-8859-2) colrange(2:9) stringcols(_all) clear
rename (v*) (state_fips county_fips location period labor_force_size employed unemployed unemployment_rate)
drop if mi(state_fips) | mi(county_fips)

* create unique countyfips
rename county_fips countyfips
rename state_fips statefips

destring statefips, replace
tostring statefips, replace
replace countyfips = statefips + countyfips
replace countyfips = subinstr(countyfips, " ", "", .)

* Create separate variables for county name and state abbreviation
split location, parse(", ")
rename (location1 location2) (county_name state_abbrev)

* Drop extraneous month
drop if regexm(period, "Dec-18") == 1

* Drop PR 
drop if statefips == "72"

* Format date variable
drop if period == ""
replace period = subinstr(period, "-", "", .)
replace period = lower(subinstr(period, " p", "", .))
replace period = subinstr(period, "(p)", "", .)
gen int date = date(period, "M20Y")
format date %td
assert ~mi(date)

* Drop extraneous variables
drop location period

* Destring variables
replace labor_force_size = subinstr(labor_force_size, ",", "", .)
replace employed = subinstr(employed, ",", "", .)
replace unemployed = subinstr(unemployed, ",", "", .)
destring statefips countyfips labor_force_size employed unemployed unemployment_rate, replace

* Order variables
order state_abbrev statefips county_name countyfips date

* Check that observations are unique 
isid statefips countyfips date
sort statefips countyfips date

gen year = year(date)
gen month = month(date)

keep if year == 2020 & inlist(month, 1, 2, 4)
rename (labor_force_size unemployment_rate)(labor_force unemp_rate)
gisid countyfips month

gen month_str = "_jan2020" if month == 1
replace month_str = "_feb2020" if month == 2
replace month_str = "_apr2020" if month == 4

keep countyfips month_str labor_force employed unemployed unemp_rate
greshape wide labor_force employed unemployed unemp_rate, i(countyfips) j(month_str)

tempfile laucntycur14
save `laucntycur14', replace

*-------------------------------------------------------------------------------
**# 3. Merge data 
*-------------------------------------------------------------------------------

use `laucntycur14', clear 

foreach year in "00" "01" "07" "10" "90" "91" {
	merge 1:1 countyfips using `laucnty`year'', nogen
}

*-------------------------------------------------------------------------------
**# 4. Export 
*-------------------------------------------------------------------------------
save "${root}/data/derived/BLS/county_employment_crisis.dta", replace 
project, creates("${root}/data/derived/BLS/county_employment_crisis.dta")
